--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Commit 6d6b40536aa49435a195fce36c4c3e4b8d96c738
Parents : 09db513
Author : Ivan <e46112d44649266d71fe2193e00a4710>
Signature : T66BB85Valid, signed by author
Date : 2026-07-26T08:28:04-05:00
feat: improve database migration process with pre-migration backup retention, schema verification, and fixture generation scripts
Changes
17 files changed, 631 insertions(+), 81 deletions(-)
Diff
diff --git a/.gitignore b/.gitignore
index 188d0999..7b3b8be6 100644
--- a/.gitignore
+++ b/.gitignore
@@ -85,6 +85,9 @@ storage/
testing/
telemetry_test_lxmf/
temp-tests/
+*.db
+*.db-wal
+*.db-shm
# Logs
*.log
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 9082c4f2..d43556b2 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -7,7 +7,7 @@ All notable changes to this project will be documented in this file.
### Added
- **Public demo mode**: Read-only mesh showcase via `MESHCHAT_DEMO_MODE` or `--demo` (blocked sends and API mutations, in-app demo banner). Optional ALTCHA v3 on login/setup, `MESHCHAT_AUTH_PAGE_HINT` for login page text, and [`docker-compose.demo.yml`](docker-compose.demo.yml) for Coolify.
-- **Database upgrades**: Automatic `backup-pre-migrate-*.zip` before schema migrations (skip with `MESHCHAT_SKIP_PRE_MIGRATE_BACKUP=1`). CLI `--list-backups` and `--export-backup` to list or copy backups for rollback.
+- **Database upgrades**: Automatic `backup-pre-migrate-*.zip` before schema migrations (skip with `MESHCHAT_SKIP_PRE_MIGRATE_BACKUP=1`). CLI `--list-backups` and `--export-backup` to list or copy backups for rollback. Post-migrate `quick_check`, structured migration logs, pre-migrate zip retention (five by default), storage lock for single-writer volumes, and N-1/N-2 fixture upgrade tests.
### Fixed
diff --git a/Taskfile.yml b/Taskfile.yml
index e4ed5a0e..f2b1092d 100644
--- a/Taskfile.yml
+++ b/Taskfile.yml
@@ -239,10 +239,16 @@ tasks:
aliases: [lint:be]
desc: Lint Python code (Ruff + basedpyright)
cmds:
+ - uv run python scripts/ci/check_schema_migrations.py
- uv run ruff check .
- uv run ruff format --check .
- uv run basedpyright -p pyrightconfig.json --level error
+ schema-fixtures:
+ desc: Regenerate schema_v{N}.db migration test fixtures when LATEST_VERSION bumps
+ cmds:
+ - uv run python scripts/ci/schema_fixture_generate.py
+
# --- Testing & Analysis ---
test:
@@ -324,6 +330,7 @@ tasks:
aliases: [test:be, test:be:full]
desc: Run Python tests (pytest, includes perf/memory profiling, same as GitHub CI)
cmds:
+ - task: schema-fixtures
- uv run pytest tests/backend -n auto {{.PYTEST_ARGS | default ""}}
test:be:cov:
diff --git a/docs/agents/skills/database-migrations-backups/SKILL.md b/docs/agents/skills/database-migrations-backups/SKILL.md
index 26b5cf3f..69d77d8e 100644
--- a/docs/agents/skills/database-migrations-backups/SKILL.md
+++ b/docs/agents/skills/database-migrations-backups/SKILL.md
@@ -15,13 +15,35 @@ Bump schema versions correctly, keep backups and snapshots safe, and never confl
- Test upgrade from an older version when the change is non-trivial.
- Heavy data backfills in migrations should skip when the target table is empty. Fresh `Database Initialization` benches create empty DBs and still run every migration step.
+### Expand-only policy (release N)
+
+- Prefer `CREATE TABLE`, `ADD COLUMN` with NULL or default, and indexes.
+- Avoid `DROP TABLE`, `DROP COLUMN`, table renames, and destructive `DELETE FROM` in the same release that new app code depends on.
+- Defer drops and renames to **N+2** (two-release lag).
+- When `LATEST_VERSION` bumps, note the schema change in `CHANGELOG.md`.
+- CI runs `scripts/ci/check_schema_migrations.py` on `schema.py` changes. Rare destructive steps need `# migration-safety: allow-destructive` on the same line.
+
## Backup and snapshot rules
- Backups skip `database-backups/` and `snapshots/` so a new zip does not nest itself (`BACKUP_SKIP_DIR_NAMES`).
- Suspicious shrink writes `backup-SUSPICIOUS-*.zip` and skips rotation. Do not treat that as a normal backup.
- Checkpoint WAL before zip snapshots when the live DB is open.
- Before applying schema upgrades (`current_version` below `LATEST_VERSION`), write `backup-pre-migrate-v*-to-v*.zip` under `database-backups/` unless `MESHCHAT_SKIP_PRE_MIGRATE_BACKUP=1`. Migration aborts if that backup fails.
+- After migrate, `PRAGMA quick_check` and `SELECT 1` must pass before the version row is updated. Failures log `schema_migration ... status=failed` and block startup.
+- Prune older `backup-pre-migrate-*.zip` files, keeping five by default (`MESHCHAT_PRE_MIGRATE_BACKUP_KEEP`, `0` disables pruning).
+- `database_version` greater than `LATEST_VERSION` raises `DatabaseTooNewError` at startup.
- Worker-thread connections must share `DatabaseProvider` pragmas (see `landlock-sqlite`).
+- One storage directory per running instance: `StorageLock` serializes migration and runtime (do not run two replicas on one `/config` volume).
+
+## Fixture workflow
+
+When `LATEST_VERSION` changes:
+
+```bash
+task schema-fixtures
+```
+
+This writes `tests/backend/fixtures/schema_versions/schema_v{N}.db` for latest, N-1, and N-2 plus `manifest.json` (manifest is committed, `*.db` files are gitignored and generated before backend tests).
## Two restore operations
@@ -38,9 +60,12 @@ Details for pickers and tutorial copy: `identity-restore`.
- `meshchatx/src/backend/database/__init__.py`
- `meshchatx/meshchat.py` (backup / restore routes, `prepare_for_database_restore`)
- `electron/offlineRecovery.js`
+- `scripts/ci/check_schema_migrations.py`
+- `scripts/ci/schema_fixture_generate.py`
## Verification
```bash
-uv run pytest tests/backend/test_database_snapshots.py tests/backend/test_schema_migration_upgrade.py -q --tb=short
+uv run python scripts/ci/check_schema_migrations.py
+uv run pytest tests/backend/test_database_snapshots.py tests/backend/test_schema_migration_upgrade.py tests/backend/test_schema_migration_matrix.py -q --tb=short
```
diff --git a/docs/en/identity-and-security.md b/docs/en/identity-and-security.md
index 45a0d5c8..4c6ec42d 100644
--- a/docs/en/identity-and-security.md
+++ b/docs/en/identity-and-security.md
@@ -87,7 +87,7 @@ Use **Blocked** for specific destination hashes. Combine with sieve filters, mes
## Data backup
-Database backups land in `database-backups/`. Before a schema upgrade, MeshChatX writes a `backup-pre-migrate-v*-to-v*.zip` in that folder unless `MESHCHAT_SKIP_PRE_MIGRATE_BACKUP=1`. Export snapshots from **About** or the API. Electron crash recovery can offer restore when integrity checks fail.
+Database backups land in `database-backups/`. Before a schema upgrade, MeshChatX writes a `backup-pre-migrate-v*-to-v*.zip` in that folder unless `MESHCHAT_SKIP_PRE_MIGRATE_BACKUP=1`. After a successful migration it runs `PRAGMA quick_check` and keeps the five newest pre-migrate zips (override with `MESHCHAT_PRE_MIGRATE_BACKUP_KEEP`, `0` disables pruning). If the stored schema version is newer than this build supports, startup refuses to migrate. Only one process should use a given identity storage directory at a time (storage lock). Roll back by restoring a backup zip and running an older MeshChatX build. Export snapshots from **About** or the API. Electron crash recovery can offer restore when integrity checks fail.
CLI examples:
diff --git a/docs/en/installation.md b/docs/en/installation.md
index 90115cdf..aa55278d 100644
--- a/docs/en/installation.md
+++ b/docs/en/installation.md
@@ -61,6 +61,8 @@ Default Compose maps `127.0.0.1:8000` on the host to port `8000` in the containe
To bind a host directory instead, mount it at `/config`. The container runs as UID 1000. The host directory must be writable by that user.
+Run only **one** MeshChatX instance per `/config` volume. Startup takes an exclusive storage lock so schema migration and runtime do not overlap. For Docker or Coolify, use a single replica on that volume and replace containers in a rolling stop-then-start order instead of two replicas sharing one config path.
+
### Public demo instance (Coolify)
For a read-only mesh showcase on [Coolify](https://coolify.io/docs/knowledge-base/docker/compose), deploy [`docker-compose.demo.yml`](../../docker-compose.demo.yml). For a normal (non-demo) Coolify deployment, use [`docker-compose.coolify.yml`](../../docker-compose.coolify.yml).
diff --git a/meshchatx.rsm b/meshchatx.rsm
index f3699f0c..c44a0f93 100644
Binary files a/meshchatx.rsm and b/meshchatx.rsm differ
diff --git a/meshchatx/meshchat.py b/meshchatx/meshchat.py
index c318df22..6b6b0b12 100644
--- a/meshchatx/meshchat.py
+++ b/meshchatx/meshchat.py
@@ -594,6 +594,7 @@ class ReticulumMeshChat:
)
self._storage_lock = None
if not skip_storage_lock:
+ # Serializes startup, schema migration, and runtime for one storage_dir.
from meshchatx.src.backend.storage_lock import StorageLock, StorageLockError
self._storage_lock = StorageLock(self.storage_dir)
diff --git a/meshchatx/src/backend/database/__init__.py b/meshchatx/src/backend/database/__init__.py
index 035eca06..55c3c6e3 100644
--- a/meshchatx/src/backend/database/__init__.py
+++ b/meshchatx/src/backend/database/__init__.py
@@ -6,6 +6,7 @@ import os
import re
import shutil
import tempfile
+import time
import zipfile
from contextlib import suppress
from datetime import UTC, datetime
@@ -25,7 +26,12 @@ from .notification_sounds import NotificationSoundDAO
from .provider import DatabaseProvider
from .ringtones import RingtoneDAO
from .rrc_room_keys import RrcRoomKeysDAO
-from .schema import DatabaseSchema, PreMigrationBackupError
+from .schema import (
+ DatabaseSchema,
+ DatabaseTooNewError,
+ PostMigrationVerificationError,
+ PreMigrationBackupError,
+)
from .sticker_packs import UserStickerPacksDAO
from .stickers import UserStickersDAO
from .telemetry import TelemetryDAO
@@ -35,6 +41,7 @@ from .voicemails import VoicemailDAO
BACKUP_BASELINE_FILENAME = "backup-baseline.json"
BACKUP_MANIFEST_NAME = "backup-manifest.json"
BACKUP_SKIP_DIR_NAMES = frozenset({"database-backups", "snapshots"})
+PRE_MIGRATE_BACKUP_PREFIX = "backup-pre-migrate-"
MIN_SIZE_RATIO = 0.2
_log = logging.getLogger("meshchatx.database")
@@ -70,6 +77,14 @@ def _sanitize_wal_checkpoint_mode(mode: str) -> str:
return m
+def _pre_migrate_backup_keep() -> int:
+ raw = os.environ.get("MESHCHAT_PRE_MIGRATE_BACKUP_KEEP", "5").strip()
+ try:
+ return max(0, int(raw))
+ except ValueError:
+ return 5
+
+
class Database:
def __init__(self, db_path):
self.provider = DatabaseProvider.get_instance(db_path)
@@ -94,25 +109,121 @@ class Database:
self.crash_history = CrashHistoryDAO(self.provider)
self.rrc_room_keys = RrcRoomKeysDAO(self.provider)
self._sqlite_memory_relaxed = False
+ self._last_pre_migrate_backup_path: str | None = None
def initialize(self):
self._tune_sqlite_pragmas()
self.schema._create_initial_tables()
current_version = self.schema.get_current_version()
target_version = DatabaseSchema.LATEST_VERSION
- if 0 < current_version < target_version:
- from meshchatx.src.env_utils import env_bool
+ backup_path: str | None = None
+ started = time.monotonic()
+ from_version = current_version
+
+ if current_version > target_version:
+ msg = (
+ f"Database version {current_version} is newer than this MeshChatX build "
+ f"supports ({target_version}). Restore a backup or upgrade the application."
+ )
+ raise DatabaseTooNewError(msg)
- if not env_bool("MESHCHAT_SKIP_PRE_MIGRATE_BACKUP", False):
- try:
- self._backup_pre_migration(current_version, target_version)
- except Exception as exc:
- msg = (
- "Pre-migration backup failed; aborting schema upgrade. "
- f"Fix disk space or permissions, or set MESHCHAT_SKIP_PRE_MIGRATE_BACKUP=1: {exc}"
- )
- raise PreMigrationBackupError(msg) from exc
- self.schema.migrate(current_version)
+ try:
+ if 0 < current_version < target_version:
+ from meshchatx.src.env_utils import env_bool
+
+ if not env_bool("MESHCHAT_SKIP_PRE_MIGRATE_BACKUP", False):
+ try:
+ result = self._backup_pre_migration(
+ current_version,
+ target_version,
+ )
+ backup_path = result.get("path")
+ self._last_pre_migrate_backup_path = backup_path
+ except Exception as exc:
+ msg = (
+ "Pre-migration backup failed; aborting schema upgrade. "
+ "Fix disk space or permissions, or set "
+ "MESHCHAT_SKIP_PRE_MIGRATE_BACKUP=1: "
+ f"{exc}"
+ )
+ raise PreMigrationBackupError(msg) from exc
+ self.schema.migrate(
+ current_version,
+ target_version,
+ update_version=False,
+ )
+ self._verify_after_migration()
+ self.schema._update_database_version_to(target_version)
+ keep = _pre_migrate_backup_keep()
+ if keep > 0:
+ self._prune_pre_migrate_backups(
+ self._identity_storage_dir(),
+ keep=keep,
+ preserve_path=backup_path,
+ )
+ except Exception as exc:
+ duration_ms = int((time.monotonic() - started) * 1000)
+ _log.info(
+ "schema_migration from=%s to=%s backup=%s duration_ms=%s status=failed error=%s",
+ from_version,
+ target_version,
+ backup_path or "",
+ duration_ms,
+ type(exc).__name__,
+ )
+ raise
+ else:
+ duration_ms = int((time.monotonic() - started) * 1000)
+ if from_version < target_version:
+ _log.info(
+ "schema_migration from=%s to=%s backup=%s duration_ms=%s status=ok",
+ from_version,
+ target_version,
+ backup_path or "",
+ duration_ms,
+ )
+
+ def _verify_after_migration(self) -> None:
+ rows = self.provider.quick_check()
+ if rows:
+ first = rows[0]
+ val = next(iter(first.values())) if isinstance(first, dict) else first[0]
+ if val != "ok":
+ msg = f"Post-migration quick_check failed: {val!s}"
+ raise PostMigrationVerificationError(msg)
+ self.provider.fetchone("SELECT 1")
+
+ @staticmethod
+ def _prune_pre_migrate_backups(
+ storage_path: str,
+ *,
+ keep: int,
+ preserve_path: str | None = None,
+ ) -> None:
+ if keep <= 0:
+ return
+ backup_dir = os.path.join(storage_path, "database-backups")
+ if not os.path.isdir(backup_dir):
+ return
+ preserve_abs = os.path.abspath(preserve_path) if preserve_path else None
+ entries: list[tuple[str, float]] = []
+ for name in os.listdir(backup_dir):
+ if not name.startswith(PRE_MIGRATE_BACKUP_PREFIX) or not name.endswith(
+ ".zip",
+ ):
+ continue
+ full = os.path.join(backup_dir, name)
+ entries.append((full, os.stat(full).st_mtime))
+ entries.sort(key=lambda item: item[1])
+ idx = 0
+ while len(entries) > keep and idx < len(entries):
+ path, _mtime = entries[idx]
+ if preserve_abs and os.path.abspath(path) == preserve_abs:
+ idx += 1
+ continue
+ with suppress(OSError):
+ os.remove(path)
+ entries.pop(idx)
def execute_sql(self, query, params=None):
return self.provider.execute(query, params)
@@ -593,7 +704,7 @@ class Database:
timestamp = datetime.now(UTC).strftime("%Y%m%d-%H%M%S")
backup_path = os.path.join(
backup_dir,
- f"backup-pre-migrate-v{from_version}-to-v{to_version}-{timestamp}.zip",
+ f"{PRE_MIGRATE_BACKUP_PREFIX}v{from_version}-to-v{to_version}-{timestamp}.zip",
)
result = self._backup_to_zip(backup_path)
print(
diff --git a/meshchatx/src/backend/database/schema.py b/meshchatx/src/backend/database/schema.py
index 0da90816..72eb2571 100644
--- a/meshchatx/src/backend/database/schema.py
+++ b/meshchatx/src/backend/database/schema.py
@@ -15,6 +15,14 @@ class PreMigrationBackupError(RuntimeError):
pass
+class PostMigrationVerificationError(RuntimeError):
+ pass
+
+
+class DatabaseTooNewError(RuntimeError):
+ pass
+
+
def _validate_identifier(name: str, label: str = "identifier") -> str:
if not _IDENTIFIER_RE.match(name):
msg = f"Invalid SQL {label}: {name!r}"
@@ -629,23 +637,22 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_debug_logs_anomaly ON debug_logs(is_anomaly)",
)
- def _update_database_version(self):
- self.provider.execute(
- """
- INSERT INTO config (key, value, created_at, updated_at)
- VALUES (?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
- ON CONFLICT(key) DO UPDATE SET
- value = EXCLUDED.value,
- updated_at = EXCLUDED.updated_at
- """,
- ("database_version", str(self.LATEST_VERSION)),
+ def migrate(
+ self,
+ current_version,
+ target_version: int | None = None,
+ *,
+ update_version: bool = True,
+ ):
+ target = (
+ self.LATEST_VERSION
+ if target_version is None
+ else min(max(0, int(target_version)), self.LATEST_VERSION)
)
-
- def migrate(self, current_version):
self._strict_migrations = True
self._migration_errors = []
try:
- self._run_migrations(current_version)
+ self._run_migrations(current_version, target)
finally:
self._strict_migrations = False
if self._migration_errors:
@@ -653,10 +660,33 @@ class DatabaseSchema:
raise DatabaseMigrationError(
f"{len(self._migration_errors)} migration step(s) failed: {first}",
)
- self._update_database_version()
+ if update_version:
+ self._update_database_version_to(target)
+
+ def migrate_up_to(self, version: int) -> None:
+ """Apply migrations up to version (for fixtures and tests)."""
+ current = self.get_current_version()
+ self.migrate(current, target_version=version)
+
+ def _update_database_version_to(self, version: int) -> None:
+ self.provider.execute(
+ """
+ INSERT INTO config (key, value, created_at, updated_at)
+ VALUES (?, ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
+ ON CONFLICT(key) DO UPDATE SET
+ value = EXCLUDED.value,
+ updated_at = EXCLUDED.updated_at
+ """,
+ ("database_version", str(version)),
+ )
+
+ def _update_database_version(self):
+ self._update_database_version_to(self.LATEST_VERSION)
- def _run_migrations(self, current_version):
- if current_version < 7:
+ def _run_migrations(self, current_version, target_version: int | None = None):
+ if target_version is None:
+ target_version = self.LATEST_VERSION
+ if current_version < 7 and target_version >= 7:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS archived_pages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -677,7 +707,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_archived_pages_hash ON archived_pages(hash)",
)
- if current_version < 8:
+ if current_version < 8 and target_version >= 8:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS crawl_tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -697,7 +727,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_crawl_tasks_page_path ON crawl_tasks(page_path)",
)
- if current_version < 9:
+ if current_version < 9 and target_version >= 9:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS lxmf_forwarding_rules (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -735,7 +765,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_lxmf_forwarding_mappings_recipient_hash ON lxmf_forwarding_mappings(final_recipient_hash)",
)
- if current_version < 10:
+ if current_version < 10 and target_version >= 10:
# Ensure unique constraints exist for ON CONFLICT clauses
# SQLite doesn't support adding UNIQUE constraints via ALTER TABLE,
# but a UNIQUE index works for ON CONFLICT.
@@ -785,13 +815,13 @@ class DatabaseSchema:
"CREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_conversation_read_state_dest_hash_unique ON lxmf_conversation_read_state(destination_hash)",
)
- if current_version < 11:
+ if current_version < 11 and target_version >= 11:
# Add is_spam column to lxmf_messages if it doesn't exist
self._safe_execute(
"ALTER TABLE lxmf_messages ADD COLUMN is_spam INTEGER DEFAULT 0",
)
- if current_version < 12:
+ if current_version < 12 and target_version >= 12:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS call_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -811,7 +841,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_call_history_timestamp ON call_history(timestamp)",
)
- if current_version < 13:
+ if current_version < 13 and target_version >= 13:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS voicemails (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -831,7 +861,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_voicemails_timestamp ON voicemails(timestamp)",
)
- if current_version < 14:
+ if current_version < 14 and target_version >= 14:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS notification_viewed_state (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -848,7 +878,7 @@ class DatabaseSchema:
"CREATE UNIQUE INDEX IF NOT EXISTS idx_notification_viewed_state_dest_hash_unique ON notification_viewed_state(destination_hash)",
)
- if current_version < 15:
+ if current_version < 15 and target_version >= 15:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS lxmf_telemetry (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -872,12 +902,12 @@ class DatabaseSchema:
"CREATE UNIQUE INDEX IF NOT EXISTS idx_lxmf_telemetry_dest_ts_unique ON lxmf_telemetry(destination_hash, timestamp)",
)
- if current_version < 16:
+ if current_version < 16 and target_version >= 16:
self._safe_execute(
"ALTER TABLE lxmf_forwarding_rules ADD COLUMN name TEXT",
)
- if current_version < 17:
+ if current_version < 17 and target_version >= 17:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS ringtones (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -890,7 +920,7 @@ class DatabaseSchema:
)
""")
- if current_version < 18:
+ if current_version < 18 and target_version >= 18:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS contacts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -907,12 +937,12 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_contacts_remote_identity_hash ON contacts(remote_identity_hash)",
)
- if current_version < 19:
+ if current_version < 19 and target_version >= 19:
self._safe_execute(
"CREATE INDEX IF NOT EXISTS idx_call_history_remote_name ON call_history(remote_identity_name)",
)
- if current_version < 20:
+ if current_version < 20 and target_version >= 20:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS notifications (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -932,7 +962,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_notifications_timestamp ON notifications(timestamp)",
)
- if current_version < 21:
+ if current_version < 21 and target_version >= 21:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS keyboard_shortcuts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -948,7 +978,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_keyboard_shortcuts_identity_hash ON keyboard_shortcuts(identity_hash)",
)
- if current_version < 22:
+ if current_version < 22 and target_version >= 22:
# Optimize fetching conversations and favorites
self._safe_execute(
"CREATE INDEX IF NOT EXISTS idx_lxmf_messages_timestamp ON lxmf_messages(timestamp)",
@@ -961,7 +991,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_announces_updated_at ON announces(updated_at)",
)
- if current_version < 23:
+ if current_version < 23 and target_version >= 23:
# Further optimize conversation fetching
self._safe_execute(
"CREATE INDEX IF NOT EXISTS idx_lxmf_messages_conv_optim ON lxmf_messages(source_hash, destination_hash, timestamp DESC)",
@@ -974,7 +1004,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_announces_aspect ON announces(aspect)",
)
- if current_version < 24:
+ if current_version < 24 and target_version >= 24:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS call_recordings (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -994,27 +1024,27 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_call_recordings_timestamp ON call_recordings(timestamp)",
)
- if current_version < 25:
+ if current_version < 25 and target_version >= 25:
# Add docs_downloaded to config if not exists
self._safe_execute(
"INSERT OR IGNORE INTO config (key, value) VALUES (?, ?)",
("docs_downloaded", "0"),
)
- if current_version < 26:
+ if current_version < 26 and target_version >= 26:
# Add initial_docs_download_attempted to config if not exists
self._safe_execute(
"INSERT OR IGNORE INTO config (key, value) VALUES (?, ?)",
("initial_docs_download_attempted", "0"),
)
- if current_version < 28:
+ if current_version < 28 and target_version >= 28:
# Add preferred_ringtone_id to contacts
self._safe_execute(
"ALTER TABLE contacts ADD COLUMN preferred_ringtone_id INTEGER DEFAULT NULL",
)
- if current_version < 29:
+ if current_version < 29 and target_version >= 29:
# Performance optimization indexes
self._safe_execute(
"CREATE INDEX IF NOT EXISTS idx_lxmf_messages_peer_hash ON lxmf_messages(peer_hash)",
@@ -1029,13 +1059,13 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_announces_updated_at ON announces(updated_at)",
)
- if current_version < 30:
+ if current_version < 30 and target_version >= 30:
# Add custom_image to contacts
self._safe_execute(
"ALTER TABLE contacts ADD COLUMN custom_image TEXT DEFAULT NULL",
)
- if current_version < 31:
+ if current_version < 31 and target_version >= 31:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS lxmf_last_sent_icon_hashes (
destination_hash TEXT PRIMARY KEY,
@@ -1045,7 +1075,7 @@ class DatabaseSchema:
)
""")
- if current_version < 32:
+ if current_version < 32 and target_version >= 32:
# Add tutorial_seen and changelog_seen_version to config
self._safe_execute(
"INSERT OR IGNORE INTO config (key, value) VALUES (?, ?)",
@@ -1056,7 +1086,7 @@ class DatabaseSchema:
("changelog_seen_version", "0.0.0"),
)
- if current_version < 33:
+ if current_version < 33 and target_version >= 33:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS debug_logs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -1079,13 +1109,13 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_debug_logs_anomaly ON debug_logs(is_anomaly)",
)
- if current_version < 34:
+ if current_version < 34 and target_version >= 34:
# Add updated_at to crawl_tasks
self._safe_execute(
"ALTER TABLE crawl_tasks ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP",
)
- if current_version < 35:
+ if current_version < 35 and target_version >= 35:
# Add lxmf_address and lxst_address to contacts
self._safe_execute(
"ALTER TABLE contacts ADD COLUMN lxmf_address TEXT DEFAULT NULL",
@@ -1094,7 +1124,7 @@ class DatabaseSchema:
"ALTER TABLE contacts ADD COLUMN lxst_address TEXT DEFAULT NULL",
)
- if current_version < 36:
+ if current_version < 36 and target_version >= 36:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS lxmf_folders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -1120,7 +1150,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_lxmf_conversation_folders_folder_id ON lxmf_conversation_folders(folder_id)",
)
- if current_version < 37:
+ if current_version < 37 and target_version >= 37:
# Add is_telemetry_trusted to contacts
self._safe_execute(
"ALTER TABLE contacts ADD COLUMN is_telemetry_trusted INTEGER DEFAULT 0",
@@ -1131,7 +1161,7 @@ class DatabaseSchema:
("telemetry_enabled", "false"),
)
- if current_version < 38:
+ if current_version < 38 and target_version >= 38:
self._safe_execute(
"ALTER TABLE lxmf_messages ADD COLUMN reply_to_hash TEXT",
)
@@ -1139,7 +1169,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_lxmf_messages_reply_to_hash ON lxmf_messages(reply_to_hash)",
)
- if current_version < 39:
+ if current_version < 39 and target_version >= 39:
# Indexes for contacts JOIN columns (used in message_handler.get_conversations
# and announce_manager.get_filtered_announces OR-based JOINs)
self._safe_execute(
@@ -1172,7 +1202,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_lxmf_messages_state_peer ON lxmf_messages(state, peer_hash)",
)
- if current_version < 40:
+ if current_version < 40 and target_version >= 40:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS crash_history (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -1191,12 +1221,12 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_crash_history_timestamp ON crash_history(timestamp)",
)
- if current_version < 41:
+ if current_version < 41 and target_version >= 41:
self._safe_execute(
"ALTER TABLE lxmf_messages ADD COLUMN attachments_stripped INTEGER DEFAULT 0",
)
- if current_version < 42:
+ if current_version < 42 and target_version >= 42:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS access_attempts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -1234,7 +1264,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_trusted_login_identity ON trusted_login_clients(identity_hash)",
)
- if current_version < 43:
+ if current_version < 43 and target_version >= 43:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS lxmf_conversation_pins (
peer_hash TEXT PRIMARY KEY NOT NULL,
@@ -1245,7 +1275,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_lxmf_conversation_pins_pinned_at ON lxmf_conversation_pins(pinned_at)",
)
- if current_version < 44:
+ if current_version < 44 and target_version >= 44:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS user_stickers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -1267,7 +1297,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_user_stickers_identity_updated ON user_stickers(identity_hash, updated_at)",
)
- if current_version < 45:
+ if current_version < 45 and target_version >= 45:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS user_gifs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -1291,7 +1321,7 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_user_gifs_identity_usage ON user_gifs(identity_hash, usage_count, last_used_at)",
)
- if current_version < 46:
+ if current_version < 46 and target_version >= 46:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS user_sticker_packs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -1344,15 +1374,15 @@ class DatabaseSchema:
"CREATE INDEX IF NOT EXISTS idx_user_stickers_pack ON user_stickers(pack_id, sort_order)",
)
- if current_version < 47:
+ if current_version < 47 and target_version >= 47:
self._ensure_column("lxmf_messages", "path_hops_at_send", "INTEGER")
self._ensure_column("lxmf_messages", "path_interface_at_send", "TEXT")
- if current_version < 48:
+ if current_version < 48 and target_version >= 48:
self._ensure_column("lxmf_messages", "path_finding_measure", "TEXT")
self._ensure_column("lxmf_messages", "path_row_hash_hex", "TEXT")
- if current_version < 49:
+ if current_version < 49 and target_version >= 49:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS notification_sounds (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -1365,7 +1395,7 @@ class DatabaseSchema:
)
""")
- if current_version < 50:
+ if current_version < 50 and target_version >= 50:
self._safe_execute("""
CREATE TABLE IF NOT EXISTS map_overlay_sources (
id INTEGER PRIMARY KEY AUTOINCREMENT,
@@ -1405,7 +1435,7 @@ class DatabaseSchema:
"ON map_overlay_sources(enabled, next_refresh_at)",
)
- if current_version < 51:
+ if current_version < 51 and target_version >= 51:
# Slim conversation list/thread loads: persist attachment flags and
# fields_meta (fields without base64 payloads) so queries never need
# to instr()/SELECT multi-MB fields blobs on the hot path.
@@ -1525,7 +1555,7 @@ class DatabaseSchema:
""",
)
- if current_version < 52:
+ if current_version < 52 and target_version >= 52:
# Materialized per-peer latest row so conversation list queries do not
# GROUP BY the full lxmf_messages table on every refresh.
self._safe_execute(
@@ -1615,7 +1645,7 @@ class DatabaseSchema:
""",
)
- if current_version < 53:
+ if current_version < 53 and target_version >= 53:
# Encrypted client-side RRC room keys for +k rooms (identity-scoped).
self._safe_execute(
"""
diff --git a/meshchatx/src/backend/storage_lock.py b/meshchatx/src/backend/storage_lock.py
index cb7dfc1c..33ba673f 100644
--- a/meshchatx/src/backend/storage_lock.py
+++ b/meshchatx/src/backend/storage_lock.py
@@ -61,6 +61,7 @@ class StorageLock:
self._soft = False
def acquire(self) -> None:
+ """Exclusive lock for one storage_dir (startup, migration, and runtime)."""
os.makedirs(self.storage_dir, exist_ok=True)
self._handle = open(self.lock_path, "a+b")
try:
@@ -89,7 +90,8 @@ class StorageLock:
self._handle.close()
self._handle = None
raise StorageLockError(
- f"Another MeshChatX instance is already using storage at {self.storage_dir}",
+ f"Another MeshChatX instance is already using storage at {self.storage_dir} "
+ f"(lock file {self.lock_path})",
) from exc
self._write_pid()
atexit.register(self.release)
@@ -108,7 +110,8 @@ class StorageLock:
self._handle.close()
self._handle = None
raise StorageLockError(
- f"Another MeshChatX instance is already using storage at {self.storage_dir}",
+ f"Another MeshChatX instance is already using storage at {self.storage_dir} "
+ f"(lock file {self.lock_path})",
)
self._write_pid()
diff --git a/scripts/ci/check_schema_migrations.py b/scripts/ci/check_schema_migrations.py
new file mode 100644
index 00000000..cf656b6c
--- /dev/null
+++ b/scripts/ci/check_schema_migrations.py
@@ -0,0 +1,96 @@
+#!/usr/bin/env python3
+# SPDX-License-Identifier: 0BSD
+
+"""Fail when schema migrations contain destructive SQL without an explicit allow tag."""
+
+from __future__ import annotations
+
+import re
+import sys
+from pathlib import Path
+
+ROOT = Path(__file__).resolve().parents[2]
+SCHEMA = ROOT / "meshchatx/src/backend/database/schema.py"
+FIXTURE_MANIFEST = ROOT / "tests/backend/fixtures/schema_versions/manifest.json"
+
+DESTRUCTIVE = re.compile(
+ r"(DROP\s+TABLE|DROP\s+COLUMN|ALTER\s+TABLE\s+.+\s+RENAME)",
+ re.IGNORECASE,
+)
+DELETE_FROM = re.compile(r"DELETE\s+FROM", re.IGNORECASE)
+DEDUP_DELETE = re.compile(r"NOT\s+IN\s*\(\s*SELECT", re.IGNORECASE)
+ALLOW_TAG = "migration-safety: allow-destructive"
+
+
+def _run_migrations_source() -> str:
+ text = SCHEMA.read_text(encoding="utf-8")
+ marker = "def _run_migrations"
+ start = text.index(marker)
+ rest = text[start:]
+ depth = 0
+ end = len(rest)
+ for i, ch in enumerate(rest):
+ if ch == "\n" and rest[i : i + 4] == "\n def " and depth == 0 and i > 20:
+ end = i
+ break
+ return rest[:end]
+
+
+def _check_schema_fixtures() -> list[str]:
+ errors: list[str] = []
+ if not FIXTURE_MANIFEST.is_file():
+ errors.append(
+ "schema fixture manifest missing; run task schema-fixtures",
+ )
+ return errors
+ import json
+
+ sys.path.insert(0, str(ROOT))
+ from meshchatx.src.backend.database.schema import DatabaseSchema
+
+ latest = DatabaseSchema.LATEST_VERSION
+ data = json.loads(FIXTURE_MANIFEST.read_text(encoding="utf-8"))
+ if int(data.get("latest_version", -1)) != latest:
+ errors.append(
+ f"schema fixtures stale (manifest latest {data.get('latest_version')}, "
+ f"code {latest}); run task schema-fixtures",
+ )
+ return errors
+
+
+def main() -> int:
+ if not SCHEMA.is_file():
+ print(f"check_schema_migrations: missing {SCHEMA}", file=sys.stderr)
+ return 1
+ violations: list[str] = []
+ for line in _run_migrations_source().splitlines():
+ stripped = line.strip()
+ if not stripped or stripped.startswith("#"):
+ continue
+ if ALLOW_TAG in line:
+ continue
+ if DESTRUCTIVE.search(line):
+ violations.append(stripped)
+ continue
+ if DELETE_FROM.search(line) and not DEDUP_DELETE.search(line):
+ violations.append(stripped)
+ if violations:
+ print(
+ "check_schema_migrations: destructive SQL in _run_migrations "
+ f"(prefer expand-only migrations or add {ALLOW_TAG} on the line):",
+ file=sys.stderr,
+ )
+ for v in violations:
+ print(f" {v}", file=sys.stderr)
+ return 1
+ fixture_errors = _check_schema_fixtures()
+ if fixture_errors:
+ print("check_schema_migrations:", file=sys.stderr)
+ for err in fixture_errors:
+ print(f" {err}", file=sys.stderr)
+ return 1
+ return 0
+
+
+if __name__ == "__main__":
+ raise SystemExit(main())
diff --git a/scripts/ci/schema_fixture_generate.py b/scripts/ci/schema_fixture_generate.py
new file mode 100644
index 00000000..5de8b04d
--- /dev/null
+++ b/scripts/ci/schema_fixture_generate.py
@@ -0,0 +1,47 @@
+#!/usr/bin/env python3
+# SPDX-License-Identifier: 0BSD
+
+"""Generate schema_v{N}.db fixtures for migration matrix tests."""
+
+from __future__ import annotations
+
+import json
+from pathlib import Path
+
+from meshchatx.src.backend.database.provider import DatabaseProvider
+from meshchatx.src.backend.database.schema import DatabaseSchema
+
+ROOT = Path(__file__).resolve().parents[2]
+FIXTURE_DIR = ROOT / "tests/backend/fixtures/schema_versions"
+MANIFEST = FIXTURE_DIR / "manifest.json"
+
+
+def main() -> int:
+ latest = DatabaseSchema.LATEST_VERSION
+ FIXTURE_DIR.mkdir(parents=True, exist_ok=True)
+ versions = [latest]
+ if latest >= 1:
+ versions.append(latest - 1)
+ if latest >= 2:
+ versions.append(latest - 2)
+ versions = sorted(set(versions))
+
+ manifest: dict[str, str] = {"latest_version": str(latest)}
+ for ver in versions:
+ db_path = FIXTURE_DIR / f"schema_v{ver}.db"
+ if db_path.exists():
+ db_path.unlink()
+ provider = DatabaseProvider(str(db_path))
+ schema = DatabaseSchema(provider)
+ schema._create_initial_tables()
+ schema.migrate_up_to(ver)
+ provider.close_all()
+ manifest[f"v{ver}"] = db_path.name
+
+ MANIFEST.write_text(json.dumps(manifest, indent=2) + "\n", encoding="utf-8")
+ print(f"Wrote fixtures for versions {versions} under {FIXTURE_DIR}")
+ return 0
+
+
+if __name__ == "__main__":
+ raise SystemExit(main())
diff --git a/tests/backend/fixtures/schema_versions/manifest.json b/tests/backend/fixtures/schema_versions/manifest.json
new file mode 100644
index 00000000..e7ad7ee2
--- /dev/null
+++ b/tests/backend/fixtures/schema_versions/manifest.json
@@ -0,0 +1,6 @@
+{
+ "latest_version": "53",
+ "v51": "schema_v51.db",
+ "v52": "schema_v52.db",
+ "v53": "schema_v53.db"
+}
diff --git a/tests/backend/test_database_lifecycle_safety.py b/tests/backend/test_database_lifecycle_safety.py
index 5e3f79d3..26b592b3 100644
--- a/tests/backend/test_database_lifecycle_safety.py
+++ b/tests/backend/test_database_lifecycle_safety.py
@@ -88,7 +88,7 @@ def test_migration_failure_does_not_bump_version(temp_dir):
("database_version", "47"),
)
- def fail_run(_current_version):
+ def fail_run(_current_version, _target_version):
schema._migration_errors.append("simulated migration failure")
schema._run_migrations = fail_run
diff --git a/tests/backend/test_database_snapshots.py b/tests/backend/test_database_snapshots.py
index dc098643..c53444c6 100644
--- a/tests/backend/test_database_snapshots.py
+++ b/tests/backend/test_database_snapshots.py
@@ -505,3 +505,60 @@ def test_pre_migration_backup_skipped_with_env(temp_dir, monkeypatch):
upgraded.close_all()
assert not any("backup-pre-migrate" in row["name"] for row in backups)
+
+
+def test_pre_migration_upgrade_logs_schema_migration_line(temp_dir, caplog):
+ import logging
+
+ from meshchatx.src.backend.database.schema import DatabaseSchema
+
+ caplog.set_level(logging.INFO, logger="meshchatx.database")
+
+ db_path = os.path.join(temp_dir, "test.db")
+ db = Database(db_path)
+ db.initialize()
+ db.close_all()
+
+ prior = DatabaseSchema.LATEST_VERSION - 1
+ if prior < 1:
+ pytest.skip("No prior schema version to simulate")
+
+ provider = DatabaseProvider(db_path)
+ provider.execute(
+ "UPDATE config SET value = ? WHERE key = ?",
+ (str(prior), "database_version"),
+ )
+ provider.close_all()
+
+ upgraded = Database(db_path)
+ upgraded.initialize()
+ upgraded.close_all()
+
+ joined = caplog.text
+ assert "schema_migration" in joined
+ assert "status=ok" in joined
+ assert f"from={prior}" in joined
+
+
+def test_prune_pre_migrate_backups_keeps_newest_five(temp_dir):
+ from meshchatx.src.backend.database import PRE_MIGRATE_BACKUP_PREFIX
+
+ backup_dir = os.path.join(temp_dir, "database-backups")
+ os.makedirs(backup_dir)
+ paths = []
+ for i in range(7):
+ name = f"{PRE_MIGRATE_BACKUP_PREFIX}v{i}-to-v{i + 1}.zip"
+ path = os.path.join(backup_dir, name)
+ with open(path, "wb") as handle:
+ handle.write(b"z")
+ os.utime(path, (1000 + i, 1000 + i))
+ paths.append(path)
+ newest = paths[-1]
+ Database._prune_pre_migrate_backups(temp_dir, keep=5, preserve_path=newest)
+ remaining = [
+ f
+ for f in os.listdir(backup_dir)
+ if f.startswith(PRE_MIGRATE_BACKUP_PREFIX) and f.endswith(".zip")
+ ]
+ assert len(remaining) == 5
+ assert os.path.basename(newest) in remaining
diff --git a/tests/backend/test_schema_migration_matrix.py b/tests/backend/test_schema_migration_matrix.py
new file mode 100644
index 00000000..3bcc26a5
--- /dev/null
+++ b/tests/backend/test_schema_migration_matrix.py
@@ -0,0 +1,162 @@
+# SPDX-License-Identifier: 0BSD
+
+"""Upgrade matrix: open schema_v{N-1} and schema_v{N-2} fixtures, run Database.initialize()."""
+
+from __future__ import annotations
+
+import json
+import shutil
+import tempfile
+import os
+from pathlib import Path
+from unittest.mock import patch
+
+import pytest
+from hypothesis import HealthCheck, given, settings
+from hypothesis import strategies as st
+
+from meshchatx.src.backend.database import (
+ Database,
+ DatabaseTooNewError,
+ PostMigrationVerificationError,
+)
+from meshchatx.src.backend.database.provider import DatabaseProvider
+from meshchatx.src.backend.database.schema import DatabaseSchema
+
+FIXTURE_DIR = Path(__file__).resolve().parent / "fixtures" / "schema_versions"
+MANIFEST = FIXTURE_DIR / "manifest.json"
+
+
+@pytest.fixture(autouse=True)
+def reset_provider():
+ DatabaseProvider._instance = None
+ yield
+ if DatabaseProvider._instance is not None:
+ DatabaseProvider._instance.close_all()
+ DatabaseProvider._instance = None
+
+
+def _fixture_path(version: int) -> Path:
+ path = FIXTURE_DIR / f"schema_v{version}.db"
+ if not path.is_file():
+ pytest.skip(
+ f"Missing fixture {path}; run scripts/ci/schema_fixture_generate.py"
+ )
+ return path
+
+
+def test_manifest_matches_latest_version():
+ if not MANIFEST.is_file():
+ pytest.skip("schema fixture manifest missing")
+ data = json.loads(MANIFEST.read_text(encoding="utf-8"))
+ assert int(data["latest_version"]) == DatabaseSchema.LATEST_VERSION
+
+
+@pytest.mark.parametrize(
+ "offset",
+ [1, 2],
+ ids=["n_minus_1", "n_minus_2"],
+)
+def test_fixture_upgrades_to_latest(tmp_path, offset):
+ target_fixture = DatabaseSchema.LATEST_VERSION - offset
+ if target_fixture < 1:
+ pytest.skip("No fixture for this offset")
+ src = _fixture_path(target_fixture)
+ db_path = tmp_path / "live.db"
+ shutil.copy2(src, db_path)
+ db = Database(str(db_path))
+ db.initialize()
+ row = db.provider.fetchone(
+ "SELECT value FROM config WHERE key = ?",
+ ("database_version",),
+ )
+ assert int(row["value"]) == DatabaseSchema.LATEST_VERSION
+ rows = db.provider.quick_check()
+ assert (
+ rows
+ and (rows[0][0] if not isinstance(rows[0], dict) else list(rows[0].values())[0])
+ == "ok"
+ )
+ db.close_all()
+
+
+def test_fixture_upgrade_writes_pre_migrate_backup(tmp_path):
+ ver = DatabaseSchema.LATEST_VERSION - 1
+ if ver < 1:
+ pytest.skip("No N-1 fixture")
+ src = _fixture_path(ver)
+ identity_dir = tmp_path / "identity"
+ identity_dir.mkdir()
+ db_path = identity_dir / "database.db"
+ shutil.copy2(src, db_path)
+ db = Database(str(db_path))
+ db.initialize()
+ backups = db.list_auto_backups(str(identity_dir))
+ db.close_all()
+ assert any("backup-pre-migrate" in b["name"] for b in backups)
+
+
+def test_post_migration_verify_failure_blocks_version_bump(tmp_path):
+ db_path = tmp_path / "verify.db"
+ db = Database(str(db_path))
+ db.schema._create_initial_tables()
+ prior = DatabaseSchema.LATEST_VERSION - 1
+ if prior < 1:
+ pytest.skip("No prior version")
+ db.schema.migrate_up_to(prior)
+ with patch.object(
+ db.provider, "quick_check", return_value=[{"quick_check": "fail"}]
+ ):
+ with pytest.raises(PostMigrationVerificationError):
+ db.initialize()
+ row = db.provider.fetchone(
+ "SELECT value FROM config WHERE key = ?",
+ ("database_version",),
+ )
+ assert int(row["value"]) == prior
+ db.close_all()
+
+
+def test_database_too_new_refuses_initialize(tmp_path):
+ db_path = tmp_path / "new.db"
+ db = Database(str(db_path))
+ db.schema._create_initial_tables()
+ future = DatabaseSchema.LATEST_VERSION + 1
+ db.provider.execute(
+ "INSERT INTO config (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value",
+ ("database_version", str(future)),
+ )
+ with pytest.raises(DatabaseTooNewError):
+ db.initialize()
+ db.close_all()
+
+
+@settings(
+ deadline=None,
+ max_examples=40,
+ suppress_health_check=[HealthCheck.function_scoped_fixture],
+)
+@given(
+ bogus=st.text(
+ alphabet=st.characters(blacklist_categories=("Cs",)),
+ min_size=0,
+ max_size=12,
+ ),
+)
+def test_bogus_database_version_config_does_not_crash_initialize(bogus):
+ if bogus.strip().isdigit():
+ bogus = "x"
+ dir_path = tempfile.mkdtemp()
+ db_path = os.path.join(dir_path, "bogus.db")
+ db = Database(str(db_path))
+ db.schema._create_initial_tables()
+ db.provider.execute(
+ "INSERT INTO config (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value",
+ ("database_version", bogus),
+ )
+ try:
+ db.initialize()
+ except (ValueError, PostMigrationVerificationError, DatabaseTooNewError):
+ pass
+ finally:
+ db.close_all()
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────